package com.ssy.lingxi.report.dao.order;

import com.ssy.lingxi.report.model.bo.tally.statistics.DemandPublishStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 需求单DAO
 * @author 伍将
 * @date 2020/12/1
 * @version 2.0.0
 */
@Service
public class RequisitionFormDAO {

    @Autowired
    @Qualifier("orderJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取需求发布统计
     * @author 伍将
     * @date 2020/12/1
     * @return:
     **/
    public List<DemandPublishStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" member_id,role_id,")
                .append(" sum(case when inner_status in (1,8,9,10) then 1 else 0 end) ToBeCommitValifyCount,")
                .append(" sum(case when inner_status = 2 then 1 else 0 end) ToBeValifyStep1Count,")
                .append(" sum(case when inner_status = 3 then 1 else 0 end) ToBeValifyStep2Count,")
                .append(" sum(case when inner_status = 4 then 1 else 0 end) ToBeCommitCount")
                .append(" from pu_need_plan")
                .append(" group by member_id,role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DemandPublishStatisticsTallyVO.class));
    }
}
